<h2>PHPExcel: Advanced Read-Write Excel Made Simple</h2>

<img src="preview.png" alt="PHPExcel: Advanced Read-Write Excel Made Simple" width="200" height="200"/>

<p>There are many excel reader / writer library all over the net. However, most of it only support partial features. Today, we'll learn a php excel library that support almost all aspect of excel. Well, except VBS, I guess.</p>

<!--more-->

<hr />
<h2>Tutorial Details</h2>
<ul>
	<li><b>Program</b>: PHPExcel</li>
	<li><b>Version</b>: 1.7.3</li>
	<li><b>Difficulty:</b> Easy</li>
	<li><b>Estimated Completion Time:20-30 minutes</b></li>
</ul>

<hr />
<h2> <span>Step 1</span> Preparation</h2>
<p>This step will tell you what we need to be able to use PHPExcel flawlessly.</p>
<h3> Dependency </h3>
<p>PHPExcel require PHP5 5.2.0 or higher. It also require php extension: php_xml, php_gd2, and optionally php_zip. Php_zip is optional because it is needed only if you want to handle .xlsx (excel 2007) and or .ods (OpenOffice Spreadsheet).</p>
<h3> Script installation </h3>
<p>If you've done with the dependency, now we can download the actual installation file <a href='http://phpexcel.codeplex.com/'>here</a>. Extract and copy <em><strong>Classes</strong></em> to your includes/libraries directory. On your script, the minimum code you need to do is:
<pre name="code" class="php">
require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';
</pre>
Then, you need to instantiate PHPExcel class:
<pre name="code" class="php">
$objPHPExcel = new PHPExcel();
$objPHPExcel-&gt;getProperties()-&gt;setCreator("Arief Bayu Purwanto")
	 -&gt;setLastModifiedBy("Arief Bayu Purwanto")
	 -&gt;setTitle("PHPExcel Test Document")
	 -&gt;setSubject("PHPExcel Test Document")
	 -&gt;setDescription("Test document for PHPExcel classes.")
	 -&gt;setCategory("Test result file");
</pre>
</p>

<hr />
<h2> <span>Step 2</span> Reading Data</h2>
<p>To read excel file, we need to instantiate the appropriate reader class. There are several class ready for reading Excel:<br />
<ul>
	<li><em><strong>PHPExcel_Reader_Excel2007</strong></em>: For reading excel 2007 format (.xlsx)</li>
	<li><em><strong>PHPExcel_Reader_Excel5</strong></em>: For reading excel 2003 format (.xls)</li>
	<li><em><strong>PHPExcel_Reader_OOCalc</strong></em>: For reading OpenOffice.Org Spreadsheet format (.ods)</li>
</ul>
</p>
<blockquote class="pullquote">
<p>
Note: You need php_zip in order to be able to read data from Excel 2007 or OOCalc format.
</p>
</blockquote>

<h3> How to actually read xls data </h3>
<p>After determining what file format we want to read, we instantiate reader class based on that. Then, we set it to only for reading and also load our xls file. 
<pre name="code" class="php">
$objReader = new PHPExcel_Reader_Excel5();
$objReader-&gt;setReadDataOnly(true);
$objPHPExcel = $objReader-&gt;load( dirname(__FILE__) . '/xls/test_data.xls' );
</pre>
We now have the reader object, next step is, instantiate the iterator.
<pre name="code" class="php">
$rowIterator = $objPHPExcel-&gt;getActiveSheet()-&gt;getRowIterator();
</pre>
After we have the iterator, we can loop through each and every row we have.
<blockquote class="pullquote right">
<p>
Oh wait, to better understand how it work, I think you should <strong>download</strong> the source file and <strong>see</strong> the xls file data first. It's located in '<em>xls/test_data.xls</em>'
</p>
</blockquote>
<pre name="code" class="php">
$array_data = array();
foreach($rowIterator as $row){
	$cellIterator = $row-&gt;getCellIterator();
	$cellIterator-&gt;setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
	if(1 == $row-&gt;getRowIndex()) continue;//skip first row
	$rowIndex = $row-&gt;getRowIndex();
	$array_data[$rowIndex] = array('A'=&gt;'', 'B'=&gt;'','C'=&gt;'','D'=&gt;'');
	
	foreach ($cellIterator as $cell) {
		if('A' == $cell-&gt;getColumn()){
			$array_data[$rowIndex][$cell-&gt;getColumn()] = $cell-&gt;getCalculatedValue();
		} else if('B' == $cell-&gt;getColumn()){
			$array_data[$rowIndex][$cell-&gt;getColumn()] = $cell-&gt;getCalculatedValue();
		} else if('C' == $cell-&gt;getColumn()){
			$array_data[$rowIndex][$cell-&gt;getColumn()] = PHPExcel_Style_NumberFormat::toFormattedString($cell-&gt;getCalculatedValue(), 'YYYY-MM-DD');
		} else if('D' == $cell-&gt;getColumn()){
			$array_data[$rowIndex][$cell-&gt;getColumn()] = $cell-&gt;getCalculatedValue();
		}
	}
}
print_r($array_data);
</pre>
Feeling a little confused? Don't worry, I'll explain it to you. The code work by looping through row iterator. Then, for each row, we instantiate cell iterator. And then, we execute code based on which column we are, right now.
</p>
<h3> Reading without cell iterator </h3>
<p>There's another alternative way to read data. Instead of using cell iterator, we simply read the data directly from currently active sheet.</p>
<pre name="code" class="php">
$sheet = $objPHPExcel-&gt;getActiveSheet();
$array_data = array();
foreach($rowIterator as $row){
	$rowIndex = $row-&gt;getRowIndex();
	$array_data[$rowIndex] = array('A'=&gt;'', 'B'=&gt;'','C'=&gt;'','D'=&gt;'');
	
	$cell = $sheet-&gt;getCell('A' . $rowIndex);
	$array_data[$rowIndex]['A'] = $cell-&gt;getCalculatedValue();
	$cell = $sheet-&gt;getCell('B' . $rowIndex);
	$array_data[$rowIndex]['B'] = $cell-&gt;getCalculatedValue();
	$cell = $sheet-&gt;getCell('C' . $rowIndex);
	$array_data[$rowIndex]['C'] = PHPExcel_Style_NumberFormat::toFormattedString($cell-&gt;getCalculatedValue(), 'YYYY-MM-DD');
	$cell = $sheet-&gt;getCell('D' . $rowIndex);
	$array_data[$rowIndex]['D'] = $cell-&gt;getCalculatedValue();
}
print_r($array_data);
</pre>
<p>What method will you use? that's depend on what situation you're facing with.</p>
<hr />
<h2> <span>Step 3</span> Writing Data </h2>
<p>Writing excel data require a slightly different route. First, we need to instantiate PHPExcel() class and get currently active sheet in which we will write into.</p>
<pre name="code" class="php">
require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel-&gt;getActiveSheet();
</pre>

<blockquote class="pullquote right">
<p>
As a note, we will write data gathered from previous step.
</p>
</blockquote>

<h3> Create row header </h3>
<p>After instantiating the needed class, our next step is to create row header.</p>

<pre name="code" class="php">
$sheet-&gt;setCellValue('A1', 'ID');
$sheet-&gt;setCellValue('B1', 'Name');
$sheet-&gt;setCellValue('C1', 'Date');
$starting_pos = ord('C');
$index_pos = 0;
foreach($array_distict_date as $date){
	$sheet-&gt;setCellValue(chr($starting_pos+$index_pos) . '2', $date);
	$index_pos++;
}
</pre>
<div class="tutorial_image"><img src="writing_header_ugly.png" alt='ugly header' border="0" /></div>
<p>Look, it's easy, right? But, it's ugly, I know. We'll fix that later. For now, let's move on to how to write the actual content.</p>
<pre name="code" class="php">
$rowcounter = 2;
foreach($array_data_user as $k1=&gt;$v1){
	$rowcounter++;
	list($uid, $uname) = explode("|", $k1);
	$sheet-&gt;setCellValue('A' . $rowcounter, $uid);
	$sheet-&gt;setCellValue('B' . $rowcounter, $uname);
	$starting_pos = ord('C');
	$index_pos = 0;
	foreach($array_distict_date as $date){
		$vdate = isset($v1[$date]) ? $v1[$date] : '-';
		$sheet-&gt;setCellValue(chr($starting_pos+$index_pos) . $rowcounter, $vdate);
		$index_pos++;
	}
}
</pre>
<p>Haha, no difference, right? Yep, no difference. It's just the same as how we write row header. Now we need to determine how we store the result.</p>
<pre name="code" class="php">
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter-&gt;save('php://output');
</pre>
<p>
You may notice that we don't choose class. Instead, we determine the file type using string parameter. Here's some acceptable value for <em><strong>PHPExcel_IOFactory::createWriter()</strong></em>:
<ul><li>Excel2007</li><li>Excel5</li><li>CSV</li><li>HTML</li><li>PDF</li></ul>
</p>
<blockquote class="pullquote">
<p>
It's also worth to note that $objWriter-&gt;save() is also accept absolute file path for writing.
</p>
</blockquote>

Now that we already know how to write excel, let's tidy it up a bit to make a professionally looking xls report.

<h3> Merging columns </h3>
<p>It's easy to merge cell. All we have to do is, determine cell range we want to merge and send it to <em><strong>PHPExcel_Worksheet::mergeCells</strong></em>:</p>
<pre name="code" class="php">
$sheet-&gt;mergeCells('A1:A2');
$sheet-&gt;mergeCells('B1:B2');
$sheet-&gt;mergeCells('C1:' . chr($starting_pos+($index_pos-1)) . '1');
</pre>
<div class="tutorial_image"><img src="merging_cells.png" alt="merged cells" border="0" /></div>

<h3> Resizing Cell Height / Width </h3>
<p>Nice! Now we need to resize date column so that no '<em>resize arrow</em>' (I don't really sure if it's the right term) appeared. Here's how we do it.</p>
<pre name="code" class="php">
$sheet->getColumnDimensionByColumn(2+$i, 2)->setWidth(10);
</pre>
<div class="tutorial_image"><img src="resizing_column.png" alt="merged cells" border="0" /></div>
<p>See? No more '<em>resize arrow</em>'.</p>
<h3> Setting text alignment </h3>
<p>We now move on to how to setting text alignment.</p>
<pre name="code" class="php">
$sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
</pre>
<div class="tutorial_image"><img src="cell_alignment.png" alt="merged cells" border="0" /></div>
<blockquote class="pullquote">
<p>
Please note, on a merged cell, we need only the top-left coordinate of that cell.
</p>
</blockquote>

<h3> Add Border And Fill Color </h3>
<p>Cool. Lets add border and background fill to it to make a more professionally looking excel report.</p>
<pre name="code" class="php">
$default_border = array(
	'style' =&gt; PHPExcel_Style_Border::BORDER_THIN,
	'color' =&gt; array('rgb'=&gt;'1006A3')
);
$style_header = array(
	'borders' =&gt; array(
		'bottom' =&gt; $default_border,
		'left' =&gt; $default_border,
		'top' =&gt; $default_border,
		'right' =&gt; $default_border,
	),
	'fill' =&gt; array(
		'type' =&gt; PHPExcel_Style_Fill::FILL_SOLID,
		'color' =&gt; array('rgb'=&gt;'E1E0F7'),
	),
	'font' =&gt; array(
		'bold' =&gt; true,
	)
);

$sheet-&gt;getStyle('A1:A2')-&gt;applyFromArray( $style_header );
$sheet-&gt;getStyle('B1:B2')-&gt;applyFromArray( $style_header );

</pre>
<div class="tutorial_image"><img src="cell_border_and_fill.png" alt="merged cells" border="0" /></div>
<hr />


<h2> Bonus section: how to work with formula </h2>
<h3> Adding Summary / Total row </h3>
<p>Working with formula is very easy. For example, we can to add TOTAL on each row, so that we know how many hour each person spent. This usually just fill certain cell with value <em><strong>'=SUM(C3:C7)'</strong></em>. How do we translate that in PHPExcel? well, guess what? fill that particular cell with whatever formula we want!</p>
<pre name="code" class="php">
//Adding total column
$ranged_total_coordinate = chr($starting_pos+$index_pos) . '1:' . chr($starting_pos+$index_pos) . '2';
$sheet-&gt;setCellValue(chr($starting_pos+$index_pos) . '1', 'Total');
$sheet-&gt;mergeCells($ranged_total_coordinate);
$sheet-&gt;getStyle(chr($starting_pos+$index_pos) . '1')-&gt;getAlignment()
		-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
		-&gt;setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet-&gt;getStyle($ranged_total_coordinate)-&gt;applyFromArray( $style_header );
$rowcounter = 2;
foreach($array_data_user as $k1=&gt;$v1){
	$rowcounter++;
	$sheet-&gt;setCellValue(chr($starting_pos+$index_pos) . $rowcounter, "=SUM(C{$rowcounter}:".chr(($starting_pos+($index_pos-1)))."{$rowcounter})");
	$sheet-&gt;getStyle(chr($starting_pos+$index_pos) . $rowcounter)-&gt;applyFromArray( $style_body_content );
}
</pre>
<div class="tutorial_image"><img src="total_column.png" border="0" /></div>
<h3> Parsing Formula </h3>
<p>What if we want to get value from certain cell, that is in formula, and we want to get the result? That's easy too!</p>
<pre name="code" class="php">
$sheet-&gt;getCell('K3')-&gt;getValue() . " -&gt; " . $sheet-&gt;getCell('K3')-&gt;getCalculatedValue();
</pre>

<hr />
<h2>Summary</h2>
<p>Working with Excel has never been this easy! we can manipulate <strong>ALMOST</strong> all aspect of an excel object. You can manipulate an excel object itself (adding properties, setting page, etc), worksheet (change column width, height), cell (playing with formula, cell styling, etc), and many more.</p>
